package com.wust.modules.online.config.util;

import com.google.api.client.util.Lists;
import com.wust.modules.online.config.config.DataBaseConfig;
import com.wust.modules.online.config.config.TableConfig;
import com.wust.modules.online.config.exception.DBException;
import com.wust.modules.online.config.service.DbTableHandle;

import java.io.ByteArrayInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.SQLSyntaxErrorException;
import java.util.ArrayList;
import java.util.EnumSet;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang.StringUtils;
import org.hibernate.HibernateException;
import org.hibernate.boot.Metadata;
import org.hibernate.boot.MetadataSources;
import org.hibernate.boot.registry.StandardServiceRegistry;
import org.hibernate.boot.registry.StandardServiceRegistryBuilder;
import org.hibernate.tool.hbm2ddl.SchemaExport;
import org.hibernate.tool.schema.TargetType;
import com.wust.modules.online.cgform.entity.OnlCgformField;

/**
 * @author wanheng
 */
@Slf4j
public class UpdateSqlUtil {

    private static DbTableHandle dbTableHandle;

    public UpdateSqlUtil() throws SQLException, DBException {
        dbTableHandle = TableUtil.getTableHandle();
    }

    public static void update(TableConfig tableConfig) throws IOException, HibernateException, SQLException, DBException {
        String databaseType = TableUtil.getDatabaseType();
        if ("ORACLE".equals(databaseType)) {
            ArrayList arrayList = Lists.newArrayList();

            OnlCgformField onlCgformField;
            for(Iterator iterator = tableConfig.getColumns().iterator(); iterator.hasNext(); arrayList.add(onlCgformField)) {
                onlCgformField = (OnlCgformField) iterator.next();
                if ("int".equals(onlCgformField.getDbType())) {
                    onlCgformField.setDbType("double");
                    onlCgformField.setDbPointLength(0);
                }
            }

            tableConfig.setColumns(arrayList);
        }

        String s = FreemarkerHelper.setUtf8("com/wust/modules/online/config/engine/tableTemplate.ftl", getMap(tableConfig, databaseType));
        log.info(s);
        HashMap hashMap = new HashMap(16);
        DataBaseConfig dataBaseConfig = tableConfig.getDataBaseConfig();
        hashMap.put("hibernate.connection.driver_class", dataBaseConfig.getDriverClassName());
        hashMap.put("hibernate.connection.url", dataBaseConfig.getUrl());
        hashMap.put("hibernate.connection.username", dataBaseConfig.getUsername());
        hashMap.put("hibernate.connection.password", dataBaseConfig.getPassword());
        hashMap.put("hibernate.show_sql", true);
        hashMap.put("hibernate.format_sql", true);
        hashMap.put("hibernate.temp.use_jdbc_metadata_defaults", false);
        hashMap.put("hibernate.dialect", TableUtil.getDialect(databaseType));
        hashMap.put("hibernate.hbm2ddl.auto", "create");
        hashMap.put("hibernate.connection.autocommit", false);
        hashMap.put("hibernate.current_session_context_class", "thread");
        StandardServiceRegistry standardServiceRegistry = (new StandardServiceRegistryBuilder()).applySettings(hashMap).build();
        MetadataSources metadataSources = new MetadataSources(standardServiceRegistry);
        ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(s.getBytes());
        metadataSources.addInputStream(byteArrayInputStream);
        Metadata metadata = metadataSources.buildMetadata();
        SchemaExport schemaExport = new SchemaExport();
        schemaExport.create(EnumSet.of(TargetType.DATABASE), metadata);
        byteArrayInputStream.close();
        List exceptions = schemaExport.getExceptions();
        Iterator iterator = exceptions.iterator();

        Exception exception;
        while(true) {
            if (!iterator.hasNext()) {
                return;
            }

            exception = (Exception) iterator.next();
            if ("java.sql.SQLSyntaxErrorException".equals(exception.getCause().getClass().getName())) {
                SQLSyntaxErrorException sqlSyntaxErrorException = (SQLSyntaxErrorException) exception.getCause();
                if ("42000".equals(sqlSyntaxErrorException.getSQLState())) {
                    continue;
                }
                break;
            } else {
                if (!"com.microsoft.sqlserver.jdbc.SQLServerException".equals(exception.getCause().getClass().getName())) {
                    break;
                }

                if (exception.getCause().toString().contains("Incorrect syntax near the keyword")) {
                    exception.printStackTrace();
                    throw new DBException(exception.getCause().getMessage());
                }

                log.error(exception.getMessage());
            }
        }

        throw new DBException(exception.getMessage());
    }

    public List<String> getUpdateSql(TableConfig tableConfig) throws DBException, SQLException {
        String databaseType = TableUtil.getDatabaseType();
        String databaseFormat = TableUtil.transferDatabaseFormat(tableConfig.getTableName(), databaseType);
        String sql = "alter table  " + databaseFormat + " ";
        ArrayList arrayList = new ArrayList();

        try {
            Map mapByFormat = this.getMapByFormat(databaseFormat);
            Map mapByConfig = this.getMapByConfig(tableConfig);
            Map mapByColumns = this.getMapByColumns(tableConfig.getColumns());
            Iterator iterator = mapByConfig.keySet().iterator();

            label72:
            while(true) {
                while(true) {
                    String s;
                    while(iterator.hasNext()) {
                        s = (String) iterator.next();
                        ColumnMeta columnMeta;
                        if (!mapByFormat.containsKey(s)) {
                            columnMeta = (ColumnMeta) mapByConfig.get(s);
                            String s1 = (String) mapByColumns.get(s);
                            if (mapByColumns.containsKey(s) && mapByFormat.containsKey(s1)) {
                                ColumnMeta columnMeta1 = (ColumnMeta) mapByFormat.get(s1);
                                String reNameFieldName = dbTableHandle.getReNameFieldName(columnMeta);
                                if ("SQLSERVER".equals(databaseType)) {
                                    arrayList.add(reNameFieldName);
                                } else {
                                    arrayList.add(sql + reNameFieldName);
                                }

                                String updateSql = this.getUpdateSql(s, columnMeta.getColumnId());
                                arrayList.add(updateSql);
                                if (!columnMeta1.equals(columnMeta)) {
                                    arrayList.add(sql + this.getUpdateSql(columnMeta, columnMeta1));
                                    if ("POSTGRESQL".equals(databaseType)) {
                                        arrayList.add(sql + this.getSpecialHandle(columnMeta, columnMeta1));
                                    }
                                }

                                if (!"SQLSERVER".equals(databaseType) && columnMeta1.isEqual(columnMeta)) {
                                    arrayList.add(this.getCommentSql(columnMeta));
                                }
                            } else {
                                arrayList.add(sql + this.getAddColumnSql(columnMeta));
                                if (!"SQLSERVER".equals(databaseType) && StringUtils.isNotEmpty(columnMeta.getComment())) {
                                    arrayList.add(this.getCommentSql(columnMeta));
                                }
                            }
                        } else {
                            columnMeta = (ColumnMeta) mapByFormat.get(s);
                            ColumnMeta columnMeta1 = (ColumnMeta) mapByConfig.get(s);
                            if (!columnMeta.isEqual(columnMeta1, databaseType)) {
                                arrayList.add(sql + this.getUpdateSql(columnMeta1, columnMeta));
                            }

                            if (!"SQLSERVER".equals(databaseType) && !"ORACLE".equals(databaseType) && columnMeta.isEqual(columnMeta1)) {
                                arrayList.add(this.getCommentSql(columnMeta1));
                            }
                        }
                    }

                    iterator = mapByFormat.keySet().iterator();

                    while(iterator.hasNext()) {
                        s = (String) iterator.next();
                        if (!mapByConfig.containsKey(s.toLowerCase()) && !mapByColumns.containsValue(s.toLowerCase())) {
                            arrayList.add(sql + this.getDropColumnSql(s));
                        }
                    }
                    break label72;
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException();
        }

        log.info(" db update sql : " + arrayList.toString());
        return arrayList;
    }

    private static Map<String, Object> getMap(TableConfig tableConfig, String s) {
        HashMap hashMap = new HashMap(4);

        for (OnlCgformField onlCgformField : tableConfig.getColumns()) {
            onlCgformField.setDbDefaultVal(addSpace(onlCgformField.getDbDefaultVal()));
        }

        hashMap.put("entity", tableConfig);
        hashMap.put("dataType", s);
        return hashMap;
    }

    private Map<String, ColumnMeta> getMapByFormat(String s) throws SQLException {
        HashMap hashMap = new HashMap();
        Connection connection = null;

        try {
            connection = TableUtil.getConnection();
        } catch (Exception e) {
            log.error(e.getMessage(), e);
        }

        assert connection != null;
        DatabaseMetaData databaseMetaData = connection.getMetaData();
        ResultSet resultSet = databaseMetaData.getColumns((String)null, null, s, "%");

        while(resultSet.next()) {
            ColumnMeta columnMeta = new ColumnMeta();
            columnMeta.setTableName(s);
            String toLowerCase = resultSet.getString("COLUMN_NAME").toLowerCase();
            columnMeta.setColumnName(toLowerCase);
            String s2 = resultSet.getString("TYPE_NAME");
            int i = resultSet.getInt("DECIMAL_DIGITS");
            String matchClassTypeByDataType = dbTableHandle.getMatchClassTypeByDataType(s2, i);
            columnMeta.setColunmType(matchClassTypeByDataType);
            int i1 = resultSet.getInt("COLUMN_SIZE");
            columnMeta.setColumnSize(i1);
            columnMeta.setDecimalDigits(i);
            String nullable = resultSet.getInt("NULLABLE") == 1 ? "Y" : "N";
            columnMeta.setIsNullable(nullable);
            String remarks = resultSet.getString("REMARKS");
            columnMeta.setComment(remarks);
            String columnDef = resultSet.getString("COLUMN_DEF");
            String columnDef1 = addSpace(columnDef) == null ? "" : addSpace(columnDef);
            columnMeta.setFieldDefault(columnDef1);
            log.info("getColumnMetadataFormDataBase --->COLUMN_NAME:" + toLowerCase.toUpperCase() + " TYPE_NAME :" + s2 + " DECIMAL_DIGITS:" + i + " COLUMN_SIZE:" + i1);
            hashMap.put(toLowerCase, columnMeta);
        }

        return hashMap;
    }

    private Map<String, ColumnMeta> getMapByConfig(TableConfig tableConfig) {
        HashMap hashMap = new HashMap();
        List columns = tableConfig.getColumns();

        for (Object column : columns) {
            OnlCgformField onlCgformField = (OnlCgformField) column;
            ColumnMeta columnMeta = new ColumnMeta();
            columnMeta.setTableName(tableConfig.getTableName().toLowerCase());
            columnMeta.setColumnId(onlCgformField.getId());
            columnMeta.setColumnName(onlCgformField.getDbFieldName().toLowerCase());
            columnMeta.setColumnSize(onlCgformField.getDbLength());
            columnMeta.setColunmType(onlCgformField.getDbType().toLowerCase());
            columnMeta.setIsNullable(onlCgformField.getDbIsNull() == 1 ? "Y" : "N");
            columnMeta.setComment(onlCgformField.getDbFieldTxt());
            columnMeta.setDecimalDigits(onlCgformField.getDbPointLength());
            columnMeta.setFieldDefault(addSpace(onlCgformField.getDbDefaultVal()));
            columnMeta.setPkType(tableConfig.getJFormPkType() == null ? "UUID" : tableConfig.getJFormPkType());
            columnMeta.setOldColumnName(onlCgformField.getDbFieldNameOld() != null ? onlCgformField.getDbFieldNameOld().toLowerCase() : null);
            log.info("getColumnMetadataFormCgForm ----> DbFieldName: " + onlCgformField.getDbFieldName().toLowerCase() + " | DbType: " + onlCgformField.getDbType().toLowerCase() + " | DbPointLength:" + onlCgformField.getDbPointLength() + " | DbLength:" + onlCgformField.getDbLength());
            hashMap.put(onlCgformField.getDbFieldName().toLowerCase(), columnMeta);
        }

        return hashMap;
    }

    private Map<String, String> getMapByColumns(List<OnlCgformField> onlCgformFields) {
        HashMap hashMap = new HashMap();
        Iterator iterator = onlCgformFields.iterator();

        while(iterator.hasNext()) {
            OnlCgformField onlCgformField = (OnlCgformField) iterator.next();
            hashMap.put(onlCgformField.getDbFieldName(), onlCgformField.getDbFieldNameOld());
        }

        return hashMap;
    }

    private String getDropColumnSql(String s) {
        return dbTableHandle.getDropColumnSql(s);
    }

    private String getUpdateSql(ColumnMeta columnMeta1, ColumnMeta columnMeta2) throws DBException {
        return dbTableHandle.getUpdateColumnSql(columnMeta1, columnMeta2);
    }

    private String getSpecialHandle(ColumnMeta columnMeta1, ColumnMeta columnMeta2) {
        return dbTableHandle.getSpecialHandle(columnMeta1, columnMeta2);
    }


    private String getAddColumnSql(ColumnMeta columnMeta) {
        return dbTableHandle.getAddColumnSql(columnMeta);
    }

    private String getCommentSql(ColumnMeta columnMeta) {
        return dbTableHandle.getCommentSql(columnMeta);
    }

    private String getUpdateSql(String s, String s1) {
        return "update onl_cgform_field set DB_FIELD_NAME_OLD = '" + s + "' where ID ='" + s1 + "'";
    }


    private static String addSpace(String s) {
        if (StringUtils.isNotEmpty(s)) {
            try {
                Double.valueOf(s);
            } catch (Exception e) {
                if (!s.startsWith("'") || !s.endsWith("'")) {
                    s = "'" + s + "'";
                }
            }
        }

        return s;
    }

    public String dropIndexs(String s1, String s2) {
        return dbTableHandle.dropIndexs(s1, s2);
    }

    public String countIndex(String s1, String s2) {
        return dbTableHandle.countIndex(s1, s2);
    }

}
